package com.manager.plat.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.manager.plat.dao.ILimitBusinessDao;
import com.manager.plat.util.BaseUtil;
import com.manager.plat.vo.LimitBusinessVo;
/**
 * 业务总量限制数据实现层
 * @author liugeng
 *
 */
public class LimitBusinessDaoImpl extends JdbcDaoSupport implements ILimitBusinessDao {
	private static Logger logger = Logger.getLogger(LimitBusinessDaoImpl.class);
	
	/**获得所有业务总量限制配置信息列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<LimitBusinessVo> getLimitBusinessList() {
		String sql = "select lb.lower_id, clp.name lower_name, lb.province, lb.day_limit, lb.month_limit " +
					 "from tb_ivr_limit_business lb " +
					 "inner join tb_ivr_config_lower_partner clp on clp.id = lb.lower_id and clp.status = 1 " +
					 "where lb.status = 1 " +
					 "group by lb.lower_id, clp.name, lb.province, lb.day_limit, lb.month_limit";
		logger.info(String.format("获得所有业务总量限制配置信息列表[%s]", sql));
		List<LimitBusinessVo> limitBusinessVoList = this.getJdbcTemplate().query(sql, rowMapper);
		
		if(limitBusinessVoList != null && limitBusinessVoList.size() > 0){
			return limitBusinessVoList;
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<LimitBusinessVo> rowMapper = new ParameterizedRowMapper<LimitBusinessVo>() {
		@Override
		public LimitBusinessVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			LimitBusinessVo limitBusinessVo = new LimitBusinessVo();
			limitBusinessVo.setLower_id(rs.getInt("lower_id"));
			limitBusinessVo.setLower_name(rs.getString("lower_name"));
			limitBusinessVo.setProvince(rs.getString("province"));
			limitBusinessVo.setDay_limit(rs.getInt("day_limit"));
			limitBusinessVo.setMonth_limit(rs.getInt("month_limit"));
			return limitBusinessVo;
		}
	};
	
	/**批量保存省份管控配置信息*/
	@Override
	public int batchInsertLimitBusiness(List<LimitBusinessVo> limitBusinessList) {
		String sql = "insert into tb_ivr_limit_business(lower_id, province, day_limit, month_limit, status) values(?,?,?,?,1)";
		int[] count = this.getJdbcTemplate().batchUpdate(sql, new autoInsertLimitBusinessPsSetter(limitBusinessList));
		if(count.length > 0){
			return count.length;
		}
		return 0;
	}
	private class autoInsertLimitBusinessPsSetter implements BatchPreparedStatementSetter{
		final List<LimitBusinessVo> limitBusinessListTemp;
		public autoInsertLimitBusinessPsSetter(List<LimitBusinessVo> limitBusinessList){
			limitBusinessListTemp = limitBusinessList;
		}
		
		@Override
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			LimitBusinessVo limitBusinessVo = limitBusinessListTemp.get(i);
			ps.setInt(1, limitBusinessVo.getLower_id());
			ps.setString(2, limitBusinessVo.getProvince());
			ps.setInt(3, limitBusinessVo.getDay_limit());
			ps.setInt(4, limitBusinessVo.getMonth_limit());
		}
		@Override
		public int getBatchSize() {
			return limitBusinessListTemp.size();
		}

	}
	/**更新渠道省份管控配置信息*/
	@Override
	public int updateLowerLimitBusiness(LimitBusinessVo limitBusinessVo) {
		String sql = "update tb_ivr_limit_business set day_limit = ?, month_limit = ? where lower_id = ?";
		Object[] param = new Object[]{limitBusinessVo.getDay_limit(), limitBusinessVo.getMonth_limit(), limitBusinessVo.getLower_id()};
		logger.info(String.format("更新渠道省份管控配置信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**根据渠道ID和省份信息获得业务总量限制信息*/
	@SuppressWarnings("unchecked")
	@Override
	public LimitBusinessVo getLimitBusinessByLowerIdAndProvince(int lower_id, String province) {
		String sql = "select lb.id, lb.lower_id, clp.name lower_name, lb.province, lb.day_limit, lb.month_limit " +
					 "from tb_ivr_limit_business lb " +
					 "inner join tb_ivr_config_lower_partner clp on clp.id = lb.lower_id and clp.status = 1 " + 
					 "where lb.lower_id = ? and lb.province = ? and lb.status = 1";
		Object[] param = new Object[]{lower_id, province};
		logger.info(String.format("根据渠道ID和省份信息获得业务总量限制信息[%s]", BaseUtil.logSQL(sql, param)));
		List<LimitBusinessVo> limitBusinessVoList = this.getJdbcTemplate().query(sql, param, tempMapper);
		
		if(limitBusinessVoList != null && limitBusinessVoList.size() > 0){
			return limitBusinessVoList.get(0);
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<LimitBusinessVo> tempMapper = new ParameterizedRowMapper<LimitBusinessVo>() {
		@Override
		public LimitBusinessVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			LimitBusinessVo limitBusinessVo = new LimitBusinessVo();
			limitBusinessVo.setId(rs.getInt("id"));
			limitBusinessVo.setLower_id(rs.getInt("lower_id"));
			limitBusinessVo.setProvince(rs.getString("province"));
			limitBusinessVo.setDay_limit(rs.getInt("day_limit"));
			limitBusinessVo.setMonth_limit(rs.getInt("month_limit"));
			return limitBusinessVo;
		}
	};
	
	/**更新长号码省份管控配置信息*/
	@Override
	public int updateLimitBusiness(LimitBusinessVo limitBusinessVo) {
		String sql = "update tb_ivr_limit_business set day_limit = ?, month_limit = ? where id = ?";
		Object[] param = new Object[]{limitBusinessVo.getDay_limit(), limitBusinessVo.getMonth_limit(), 
									limitBusinessVo.getId()};
		logger.info(String.format("更新渠道省份管控配置信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**删除业务总量限制信息*/
	@Override
	public int deleteLimitBusiness(int lower_id, String province) {
		String sql = "update tb_ivr_limit_business set status = 0 where lower_id = ? and province = ?";
		Object[] param = new Object[]{lower_id, province};
		logger.info(String.format("删除业务总量限制信息[%s]", BaseUtil.logSQL(sql, param)));

		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**删除渠道省份管控配置信息*/
	@Override
	public int deleteLowerLimitBusiness(int lower_id) {
		String sql = "update tb_ivr_limit_business set status = 0 where lower_id = ?";
		Object[] param = new Object[]{lower_id};
		logger.info(String.format("删除渠道省份管控配置信息[%s]", BaseUtil.logSQL(sql, param)));

		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**根据渠道ID获得该渠道下的所有省份*/
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getLowerLimitBusinessProvinceById(int lower_id) {
		String sql = "select province from tb_ivr_limit_business where lower_id = ? and status = 1 group by province";
		Object[] param = new Object[]{lower_id};
		logger.info(String.format("根据渠道ID获得该渠道下的所有省份[%s]", BaseUtil.logSQL(sql, param)));

		List<String> provinceList = this.getJdbcTemplate().query(sql, param, provinceMapper);
		
		if(provinceList != null && provinceList.size() > 0){
			return provinceList;
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<String> provinceMapper = new ParameterizedRowMapper<String>() {
		@Override
		public String mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getString("province");
		}
	};
}
